package com.lablzh.gdxy.service.impl;

import com.lablzh.gdxy.component.DynamicOperateExcelUtils;
import com.lablzh.gdxy.component.UserDetailsUtil;
import com.lablzh.gdxy.entity.*;
import com.lablzh.gdxy.entity.admin.Major;
import com.lablzh.gdxy.entity.admin.Parameter;
import com.lablzh.gdxy.entity.design.Design;
import com.lablzh.gdxy.entity.design.DesignApply;
import com.lablzh.gdxy.entity.design.StudentDesign;
import com.lablzh.gdxy.entity.practice.StudentPractice;
import com.lablzh.gdxy.repository.*;
import com.lablzh.gdxy.repository.admin.DepartmentRepository;
import com.lablzh.gdxy.repository.admin.MajorRepository;
import com.lablzh.gdxy.repository.admin.ParameterRepository;
import com.lablzh.gdxy.repository.design.DesignApplyRepository;
import com.lablzh.gdxy.repository.design.DesignRepository;
import com.lablzh.gdxy.repository.design.StudentDesignRepositoty;
import com.lablzh.gdxy.service.DesignService;
import com.lablzh.gdxy.service.FileService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.CacheEvict;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.cache.annotation.Caching;
import org.springframework.data.domain.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.time.LocalDate;
import java.util.*;

@Transactional
@Service
public class DesignServiceImpl implements DesignService {
    private static final String BASEPATH = "./resources/static/";
    @Autowired
    private DesignRepository designRepository;
    @Autowired
    private StudentRepositoty studentRepositoty;
    @Autowired
    private UserDetailsUtil userDetailsUtil;
    @Autowired
    private DesignApplyRepository designApplyRepository;
    @Autowired
    private StudentDesignRepositoty studentDesignRepositoty;
    @Autowired
    private FileService fileService;
    @Autowired
    private ParameterRepository parameterRepository;
    @Autowired
    private TeacherRepository teacherRepository;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private MajorRepository majorRepository;

    @Override
    @Caching(evict = {@CacheEvict(cacheNames = "design", allEntries = true),
            @CacheEvict(cacheNames = "teacher", allEntries = true)})
    public String applyCreate(Design design) {
//        if(designRepository.findByName(design.getName())!=null){
//            return "该毕业设计已存在！";
//        }
        //读互选参数表
        Parameter parameter = parameterRepository.findByCollegeAndGradeAndType(design.getCollege(), design.getGrade(), "毕业设计");
        Teacher teacher = teacherRepository.findAllByUsername(design.getTeacherUsername());
        if (parameter == null) {
            return "教务员未设置初始互选参数！";
        }
        //初始化毕业设计表
        design.setStatus("未开放");
        design.setAudit("未审");
        String department = teacher.getDepartment();
        design.setAuditor(departmentRepository.findByName(department).getTeachingDirector());
        design.setApplyed(0);
        design.setRecv(0);
        design.setTransToSummit(0);
        design.setTransSummited(0);
        design.setTransToReview(0);
        design.setTransReviewed(0);
        design.setReportToSummit(0);
        design.setReportSummited(0);
        design.setReportToReview(0);
        design.setReportReviewed(0);
        design.setPaperToSummit(0);
        design.setPaperSummited(0);
        design.setPaperToReview(0);
        design.setPaperReviewed(0);
        design.setTitle(teacher.getTitle());
        design.setAddTime(LocalDate.now());
        design.setTaskStart(parameter.getTaskStart());
        design.setTaskDdl(parameter.getTaskDdl());
        design.setStuChooseStart(parameter.getStuChooseStart());
        design.setStuChooseDdl(parameter.getStuChooseDdl());
        design.setTeaChooseStart(parameter.getTeaChooseStart());
        design.setTeaChooseDdl(parameter.getTeaChooseDdl());
        design.setTransDdl(parameter.getTransDdl());
        design.setOpenDdl(parameter.getOpenDdl());
        design.setDesignDdl(parameter.getDesignDdl());
        design.setMaxApply(parameter.getMaxApply());
        design.setMaxRecv(parameter.getMaxRecv());
        if (designRepository.save(design) != null) {
            if ("校内".equals(design.getInorout())) {
                teacher.setDesignInY(teacher.getDesignInY() + 1);
            } else {
                teacher.setDesignOutY(teacher.getDesignOutY() + 1);
            }
            teacherRepository.save(teacher);
            return "申请成功！";
        } else {
            return "申请失败！";
        }
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String updateTeam(Design design) {
        designRepository.save(design);
        return "修改成功！";
    }

    @Override
    @Caching(evict = {@CacheEvict(cacheNames = "design", allEntries = true),
            @CacheEvict(cacheNames = "teacher", allEntries = true)})
    public String deleteTeam(Integer id) {
        designApplyRepository.deleteAllByDesignID(id);
        studentDesignRepositoty.deleteAllByDesignID(id);//删除该课程设计的学生
        Design design = designRepository.findById(id).orElse(null);
        designRepository.deleteById(id);
        assert design != null;
        Teacher teacher = teacherRepository.findAllByUsername(design.getTeacherUsername());
        //老师已出题数-1
        if (teacher != null) {
            if ("校内".equals(design.getInorout())) {
                int temp = teacher.getDesignInY() - 1;
                teacher.setDesignInY(temp < 0 ? 0 : temp);
            } else {
                int temp = teacher.getDesignOutY() - 1;
                teacher.setDesignOutY(temp < 0 ? 0 : temp);
            }
            teacherRepository.save(teacher);
        }


        return "删除成功！";
    }


    @Override
    @Cacheable(cacheNames = "design")
    public List<Design> getAllTeam() {
        return designRepository.findAll(Sort.by(Sort.Direction.DESC, "id"));
    }

    @Override
    public Page<Design> queryDesignSelective(Integer page, Integer limit) {
        Pageable pageable = PageRequest.of(page, limit, Sort.by(Sort.Direction.DESC, "id"));

        return designRepository.findAll(pageable);
    }

    @Override
    public List<Design> getAudited(String audit) {
        return designRepository.findAllByAudit(audit, Sort.by(Sort.Direction.DESC, "id"));
    }

    @Override
    @Cacheable(cacheNames = "design")
    public Page<Design> getAudited(String audit, Integer page, Integer limit) {
        Pageable pageable = PageRequest.of(page, limit, Sort.by(Sort.Direction.DESC, "id"));
        return designRepository.findAllByAudit(audit, pageable);
    }

    @Override
    public List<Design> teamTeacher() {
        String username = userDetailsUtil.getUserDetails();
        return designRepository.findAllByTeacherUsername(username, Sort.by(Sort.Direction.DESC, "id"));
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String updateStatus(Integer id, String status) {
        if (designRepository.updateStatusById(id, status) != 0) {
            return "修改成功！";
        } else {
            return "修改失败！";
        }
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String updateAudit(Integer id, String audit) {
        if ("未审".equals(audit) && designApplyRepository.findAllByDesignID(id).size() != 0) {
            return "该课设已有学生申请，不能反审！";
        }
        if (designRepository.updateAuditById(id, audit) != 0) {
            return "修改成功！";
        } else {
            return "修改失败！";
        }
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String apply(Integer id) {
        String username = userDetailsUtil.getUserDetails();
        Student student = studentRepositoty.findAllByStID(username);
        Design design = designRepository.findById(id).orElse(null);
        assert design != null;
        if (!design.getAudit().equals("已审")) {
            return "当前题目未被审核通过，无法申请！";
        }
        if (design.getMaxApply() <= design.getApplyed()) {
            return "选题申请已达上限！";
        }
        //判断时间
        LocalDate now = LocalDate.now();
        if (now.isBefore(design.getStuChooseStart())) {
            return "选题未开始！";
        }
        if (now.isAfter(design.getStuChooseDdl())) {
            return "选题已结束！";
        }
        //初始化申请表
        DesignApply designApply = new DesignApply();
        designApply.setDesignID(id);
        designApply.setDesignName(design.getName());
        designApply.setStudentUsername(student.getStID());
        designApply.setStudentName(student.getName());
        designApply.setTeacher(design.getTeacherUsername());
        designApply.setCollege(design.getCollege());
        designApply.setMajor(student.getMajor());
        designApply.setGrade(design.getGrade());
        designApply.setStatus("未审");

        if (designApplyRepository.findByStudentUsernameAndDesignID(student.getStID(), id) != null) {
            return "请不要重复申请！";
        }
        if (designApplyRepository.save(designApply) != null) {
            designRepository.updateApplyedById(id, design.getApplyed() + 1);
            return "申请成功！";
        }
        return "申请失败！";
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String undo(Integer id) {
        DesignApply designApply = designApplyRepository.findById(id).orElse(null);
        if (designApply == null) {
            return "不存在该申请！";
        }
        if (!designApply.getStudentUsername().equals(userDetailsUtil.getUserDetails())) {
            return "删除失败！";
        }
        if ("已通过".equals(designApply.getStatus())) {
            return "该申请已通过，不能取消！";
        }
        int applyed=designRepository.findById(designApply.getDesignID()).orElse(null).getApplyed();
        if("未审".equals(designApply.getStatus()))
        {
            applyed--;
        }
        designApplyRepository.deleteById(id);
        designRepository.updateApplyedById(designApply.getDesignID(),
                applyed);
        return "删除成功！";
    }

    @Override
    public List<DesignApply> applyTeacher(Integer id) {
        String username = userDetailsUtil.getUserDetails();
        return designApplyRepository.findAllByTeacherAndDesignID(username, id);
    }

    @Override
    public List<DesignApply> applyStudent() {
        String username = userDetailsUtil.getUserDetails();
        return designApplyRepository.findAllByStudentUsername(username);
    }

    @Override
    public String deleteStudent(Integer id) {
        DesignApply designApply = designApplyRepository.findById(id).orElse(null);
        Design design = designRepository.findById(designApply.getDesignID()).orElse(null);
        //将毕设表已确认名额减一
        designRepository.updateRecvById(design.getId(), design.getRecv() - 1);
        //将未提交报告和未批阅人数减一
        designRepository.updateTransToSummitAndReportToSummitAndPaperToSummitAndTransToReviewAndReportToReviewAndPaperToReviewById(
                design.getId(), design.getTransToSummit() - 1,
                design.getReportToSummit() - 1,
                design.getPaperToSummit() - 1,
                design.getTransToReview() - 1,
                design.getReportToReview() - 1,
                design.getPaperToReview() - 1);
        //删除学生课设
        studentDesignRepositoty.deleteByDesignIDAndStudentUsername(design.getId(), designApply.getStudentUsername());
        //删除学生课设申请
        designApplyRepository.deleteById(id);
        return "删除成功！";
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String confirm(Integer id) {
        if (designApplyRepository.findById(id).get().getStatus().equals("已通过")) {
            return "已审核过！";
        }
        LocalDate now = LocalDate.now();
        DesignApply designApply = designApplyRepository.findById(id).orElse(null);
        assert designApply != null;
        Student student = studentRepositoty.findAllByStID(designApply.getStudentUsername());
        Design design = designRepository.findById(designApply.getDesignID()).orElse(null);
        assert design != null;
        //判断
        if (design.getMaxRecv() <= design.getRecv()) {
            return "选题确认已达上限！";
        }
        //判断时间
        if (now.isBefore(design.getTeaChooseStart())) {
            return "教师选择未开始！";
        }
        if (now.isAfter(design.getTeaChooseDdl())) {
            return "教师选择已结束！";
        }
        if (designApplyRepository.updateStatusById(id, "已通过") == 0) {
            return "修改失败！";
        }
        //初始化学生毕设表
        StudentDesign studentDesign = new StudentDesign();
        studentDesign.setDesignID(designApply.getDesignID());
        studentDesign.setDesignName(designApply.getDesignName());
        studentDesign.setStudentUsername(designApply.getStudentUsername());
        studentDesign.setTeacherUsername(design.getTeacherUsername());
        studentDesign.setStudentName(designApply.getStudentName());
        studentDesign.setCollege(design.getCollege());
        studentDesign.setMajor(student.getMajor());
        studentDesign.setGrade(design.getGrade());
        studentDesign.setClasses(student.getClasses());
        studentDesign.setDepartment(teacherRepository.findAllByUsername(design.getTeacherUsername()).getDepartment());
        studentDesign.setTransSummit("未提交");
        studentDesign.setReportSummit("未提交");
        studentDesign.setPaperSummit("未提交");
        studentDesign.setTransReview("未批阅");
        studentDesign.setReportReview("未批阅");
        studentDesign.setPaperReview("未批阅");
        studentDesignRepositoty.save(studentDesign);
        //将毕设表已确认名额加一
        designRepository.updateRecvById(design.getId(), design.getRecv() + 1);
        //将未提交报告和未批阅人数加一
        designRepository.updateTransToSummitAndReportToSummitAndPaperToSummitAndTransToReviewAndReportToReviewAndPaperToReviewById(
                design.getId(), design.getTransToSummit() + 1,
                design.getReportToSummit() + 1,
                design.getPaperToSummit() + 1,
                design.getTransToReview() + 1,
                design.getReportToReview() + 1,
                design.getPaperToReview() + 1);
        return "保存成功！";
    }

    @Override
    public String refuse(Integer id) {
        if (designApplyRepository.updateStatusById(id, "未通过") == 0) {
            return "修改失败！";
        }
        DesignApply designApply = designApplyRepository.findById(id).get();
        Integer designID = designApply.getDesignID();
        Design design = designRepository.findById(designID).orElse(null);
        String studentUsername = designApply.getStudentUsername();
        StudentDesign studentDesign = studentDesignRepositoty.findByDesignIDAndStudentUsername(designID, studentUsername);

        if (studentDesign != null) {
            if ("已提交".equals(studentDesign.getReportSummit()) ||
                    "已提交".equals(studentDesign.getPaperSummit()) ||
                    "已提交".equals(studentDesign.getTransSummit())) {
                return "报告已提交，不能退回！";
            }
            assert design != null;
            designRepository.updateTransToSummitAndReportToSummitAndPaperToSummitAndTransToReviewAndReportToReviewAndPaperToReviewById(design.getId(),
                    design.getTransToSummit() - 1,
                    design.getReportToSummit() - 1,
                    design.getPaperToSummit() - 1,
                    design.getTransToReview() - 1,
                    design.getReportToReview() - 1,
                    design.getPaperToReview() - 1);
            designRepository.updateApplyedById(designID,design.getApplyed()-1);
        }
        studentDesignRepositoty.deleteByDesignIDAndStudentUsername(designApply.getDesignID(), designApply.getStudentUsername());
        return "修改成功！";

    }

    @Override
    public List<StudentDesign> getDesignStudent() {
        String username = userDetailsUtil.getUserDetails();
        return studentDesignRepositoty.findAllByStudentUsername(username);
    }

    @Override
    public List<StudentDesign> getChoosed(Integer id) {
        return studentDesignRepositoty.findAllByDesignID(id);
    }

    @Override
    public List<StudentDesign> getStudentByTransSummit(String summit, Integer id) {
        return studentDesignRepositoty.findAllByDesignIDAndTransSummit(id, summit);
    }

    @Override
    public List<StudentDesign> getStudentByReportSummit(String summit, Integer id) {
        return studentDesignRepositoty.findAllByDesignIDAndReportSummit(id, summit);
    }

    @Override
    public List<StudentDesign> getStudentByTransReview(String review, Integer id) {
        return studentDesignRepositoty.findAllByDesignIDAndTransReview(id, review);
    }

    @Override
    public List<StudentDesign> getStudentByReportReview(String review, Integer id) {
        return studentDesignRepositoty.findAllByDesignIDAndReportReview(id, review);
    }

    @Override
    public List<StudentDesign> getStudentByPaperSummit(String summit, Integer id) {
        return studentDesignRepositoty.findAllByDesignIDAndPaperSummit(id, summit);
    }

    @Override
    public List<StudentDesign> getStudentByPaperReview(String review, Integer id) {
        return studentDesignRepositoty.findAllByDesignIDAndPaperReview(id, review);
    }

    @Override
    public Map<String, Object> queryPerformance(StudentDesign studentDesign, int page) {
        ExampleMatcher exampleMatcher = ExampleMatcher.matching()
                .withMatcher("designName", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("college", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("grade", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("major", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("studentName", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("classes", ExampleMatcher.GenericPropertyMatchers.contains());


        Example<StudentDesign> example = Example.of(studentDesign, exampleMatcher);
        Pageable studentDesignPage = PageRequest.of(page - 1, 20, Sort.by(Sort.Direction.DESC, "id"));
        Page<StudentDesign> all = studentDesignRepositoty.findAll(example, studentDesignPage);
        Map<String, Object> data = new HashMap<>();
        data.put("totalPage", all.getTotalPages());
        data.put("total", all.getTotalElements());
        data.put("studentDesign", all.getContent());
        return data;
    }

    @Override
    public void downloadExcel(StudentDesign studentDesign1, HttpServletRequest request, HttpServletResponse response, String sort) throws FileNotFoundException {
        //条件查询
        ExampleMatcher exampleMatcher = ExampleMatcher.matching()
                .withMatcher("designName", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("college", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("grade", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("major", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("studentName", ExampleMatcher.GenericPropertyMatchers.contains())
                .withMatcher("classes", ExampleMatcher.GenericPropertyMatchers.contains());
        Example<StudentDesign> example = Example.of(studentDesign1, exampleMatcher);
        List<StudentDesign> studentDesigns = studentDesignRepositoty.findAll(example);
        //排序
        if (sort.equals("college")) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getCollege));
        } else if (sort.equals("DesignName")) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getDesignName));
        } else if (sort.equals("studentName")) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getStudentName));
        } else if (sort.equals("studentUsername")) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getStudentUsername));
        } else if (sort.equals("major")) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getMajor));
        } else if (sort.equals("classes")) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getClasses));
        }
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(BASEPATH + "毕业设计/" + "毕业设计学生汇总成绩表模板.xlsx");
        List<Map<String, Object>> datas = new ArrayList<>();
        for (StudentDesign studentDesign : studentDesigns) {
            Map<String, Object> data = new HashMap<>();
            data.put("题目名称", studentDesign.getDesignName());
            data.put("院系", studentDesign.getCollege());
            data.put("专业", studentDesign.getMajor());
            data.put("班级", studentDesign.getClasses());
            data.put("学号", studentDesign.getStudentUsername());
            data.put("姓名", studentDesign.getStudentName());
            data.put("综合评分", studentDesign.getScore());
            data.put("课堂表现成绩", studentDesign.getClassLevel());
            data.put("课堂表现评分", studentDesign.getClassScore());
            data.put("课堂表现比例", studentDesign.getClassProportion());
            data.put("开题报告成绩", studentDesign.getReportLevel());
            data.put("开题报告评分", studentDesign.getReportScore());
            data.put("开题报告比例", studentDesign.getReportProportion());
            data.put("英文翻译成绩", studentDesign.getTransLevel());
            data.put("英文翻译评分", studentDesign.getTransScore());
            data.put("英文翻译比例", studentDesign.getTransProportion());
            data.put("作业报告成绩", studentDesign.getPaperLevel());
            data.put("作业报告评分", studentDesign.getPaperScore());
            data.put("作业报告比例", studentDesign.getPaperProportion());

            datas.add(data);
        }
        if (!datas.isEmpty()) {
            dynamicOperateExcelUtils.appendRows(5, datas,
                    new String[]{"题目名称", "院系", "专业", "班级", "学号", "姓名", "综合评分",
                            "课堂表现成绩", "课堂表现评分", "课堂表现比例", "开题报告成绩", "开题报告评分", "开题报告比例",
                            "英文翻译成绩", "英文翻译评分", "英文翻译比例", "作业报告成绩", "作业报告评分", "作业报告比例"});
        } else {
            dynamicOperateExcelUtils.replaceCellValue(5, 0, "无相关记录");
        }

        dynamicOperateExcelUtils.exportExcel(new File(BASEPATH + "毕业设计学生汇总成绩表.xlsx"));
        fileService.downloadFile("毕业设计学生汇总成绩表.xlsx", request, response);
        fileService.deleteFile(BASEPATH + "毕业设计学生汇总成绩表.xlsx");
    }

    @Override
    public void downloadChoose(String status, String college, String major, String grade, HttpServletRequest request, HttpServletResponse response) throws FileNotFoundException {
        List<StudentID> studentIDS = null;
        if ("".equals(major)) {
            studentIDS = designApplyRepository.findAllByStatusAndCollegeAndGrade(status, college, grade);
        } else {
            studentIDS = designApplyRepository.findAllByStatusAndCollegeAndMajorAndGrade(status, college, major, grade);
        }

        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(BASEPATH + "毕业设计/" + "学生选题情况统计报表模板.xlsx");
        List<Map<String, Object>> datas = new ArrayList<>();
        for (StudentID studentID : studentIDS) {
            Map<String, Object> data = new HashMap<>();
            Student student = studentRepositoty.findAllByStID(studentID.getStudentUsername());
            data.put("院系", student.getCollege());
            data.put("专业", student.getMajor());
            data.put("班级", student.getCollege());
            data.put("学号", student.getStID());
            data.put("姓名", student.getName());
            data.put("联系电话", student.getPhone());
            data.put("互选状态", status);
            datas.add(data);
        }
        dynamicOperateExcelUtils.replaceCellValue(0, 0, college + grade + status + "毕业设计学生互选统计表");
        if (!datas.isEmpty()) {
            dynamicOperateExcelUtils.appendRows(5, datas,
                    new String[]{"院系", "专业", "班级", "学号", "姓名", "联系电话", "互选状态"});
        } else {
            dynamicOperateExcelUtils.replaceCellValue(5, 0, "无相关记录");
        }
        String fileName = college + "_" + grade + "_" + status + "毕业设计学生互选统计表.xlsx";
        dynamicOperateExcelUtils.exportExcel(new File(BASEPATH + fileName));
        fileService.downloadFile(fileName, request, response);
        fileService.deleteFile(BASEPATH + fileName);
    }

    //课题安排统计表
    @Override
    public void dowloadArrange(String college, String grade, HttpServletResponse response, HttpServletRequest request) throws FileNotFoundException {
        Map<String, Integer> counts = new HashMap<>();
        counts.put("paper", 0);
        counts.put("professor", 0);
        counts.put("associateProfessor", 0);
        counts.put("lecturer", 0);
        counts.put("assistant", 0);
        counts.put("studentNumber", 0);
        counts.put("gcsj", 0);//工程设计
        counts.put("ztyj", 0);//专题研究
        counts.put("zhsy", 0);//综合试验
        counts.put("lwzs", 0);//论文综述
        counts.put("qt", 0);//其他
        List<Map<String, Object>> datas = new ArrayList<>();
        List<StudentDesign> studentDesigns = null;
        List<Major> majors = majorRepository.findAllByCollege(college);
        for (Major major : majors) {
            studentDesigns = studentDesignRepositoty.findAllByCollegeAndMajorAndGrade(college, major.getName(), grade);
            Map<String, Integer> result = count(studentDesigns);
            for (String key : result.keySet()) {
                counts.put(key, counts.get(key) + result.get(key));
            }
            Map<String, Object> data = new HashMap<>();
            data.put("专业", major.getName());
            data.put("学生人数", result.get("studentNumber"));
            data.put("课题数", result.get("paper"));
            data.put("结合实际的课题数", "");
            data.put("结合教师科研课题数", "");
            data.put("工程设计", result.get("gcsj"));
            data.put("专题研究", result.get("ztyj"));
            data.put("综合试验", result.get("zhsy"));
            data.put("论文综述", result.get("lwzs"));
            data.put("其他", result.get("qt"));
            data.put("教授", result.get("professor"));
            data.put("副教授/高级工程师", result.get("associateProfessor"));
            data.put("讲师/工程师", result.get("lecturer"));
            data.put("助教", result.get("assistant"));
            data.put("上机时数", "");
            datas.add(data);
        }
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(BASEPATH + "毕业设计/" + "课题安排统计表模板.xlsx");
        dynamicOperateExcelUtils.replaceCellValue(0, 0, college + grade + "课题安排统计表");
        if (!datas.isEmpty()) {
            System.out.println("datas:" + datas);
            dynamicOperateExcelUtils.appendRows(4, datas,
                    new String[]{"专业", "学生人数", "课题数", "结合实际的课题数", "结合教师科研课题数",
                            "工程设计", "专题研究", "综合试验", "论文综述", "其他", "教授", "副教授/高级工程师", "讲师/工程师",
                            "助教", "上机时数"});
        } else {
            dynamicOperateExcelUtils.replaceCellValue(5, 0, "无相关记录");
        }
        int row = datas.size() + 4;
        dynamicOperateExcelUtils.replaceCellValue(row, 0, "专业");
        dynamicOperateExcelUtils.replaceCellValue(row, 1, counts.get("studentNumber"));
        dynamicOperateExcelUtils.replaceCellValue(row, 2, counts.get("paper"));
        dynamicOperateExcelUtils.replaceCellValue(row, 5, counts.get("gcsj"));
        dynamicOperateExcelUtils.replaceCellValue(row, 6, counts.get("ztyj"));
        dynamicOperateExcelUtils.replaceCellValue(row, 7, counts.get("zhsy"));
        dynamicOperateExcelUtils.replaceCellValue(row, 8, counts.get("lwzs"));
        dynamicOperateExcelUtils.replaceCellValue(row, 9, counts.get("qt"));
        dynamicOperateExcelUtils.replaceCellValue(row, 10, counts.get("professor"));
        dynamicOperateExcelUtils.replaceCellValue(row, 11, counts.get("associateProfessor"));
        dynamicOperateExcelUtils.replaceCellValue(row, 12, counts.get("lecturer"));
        dynamicOperateExcelUtils.replaceCellValue(row, 13, counts.get("assistant"));
        String fileName = college + "_" + grade + "课题安排统计表.xlsx";
        dynamicOperateExcelUtils.exportExcel(new File(BASEPATH + fileName));
        fileService.downloadFile(fileName, request, response);
        fileService.deleteFile(BASEPATH + fileName);
    }


    //课题安排表
    @Override
    public void downloadArrangeByMajor(String college, String major, String department, String grade, String sort, HttpServletRequest request, HttpServletResponse response) throws FileNotFoundException {
        System.out.println("============");
        System.out.println("params:" + college + ":" + major + ":" + department + ":" + grade + ":" + sort);
        System.out.println("============");
        List<StudentDesign> studentDesigns = null;
        if ("".equals(department)) {
            studentDesigns = studentDesignRepositoty.findAllByCollegeAndMajorAndGrade(college, major, grade);
        } else {
            studentDesigns = studentDesignRepositoty.findAllByCollegeAndDepartmentAndGrade(college, department, grade);
        }
//        studentDesigns = studentDesignRepositoty.findAllByCollegeAndMajorAndGrade(college, major, grade);
        System.out.println("============");
        System.out.println("studentDesign:" + studentDesigns);
        System.out.println("============");
        List<Map<String, Object>> datas = new ArrayList<>();
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(BASEPATH + "毕业设计/" + "课题安排表模板.xlsx");
        Map<String, Integer> result = count(studentDesigns);
        //排序方式
        if ("按_系别、老师姓名_排序".equals(sort)) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getDepartment).thenComparing(StudentDesign::getTeacherUsername));
        } else if ("按_班级、学生学号_排序".equals(sort)) {
            studentDesigns.sort(Comparator.comparing(StudentDesign::getClasses).thenComparing(StudentDesign::getStudentUsername));
        }
        for (StudentDesign studentDesign : studentDesigns) {
            Design design = designRepository.findById(studentDesign.getDesignID()).orElse(null);
            System.out.println("============");
            System.out.println("design:" + design);
            System.out.println("============");
            Map<String, Object> data = new HashMap<>();
            assert design != null;
            data.put("姓名", design.getTeacherUsername());
            data.put("职称", design.getTitle());
            data.put("毕业设计论文题目", studentDesign.getPaperName());
            data.put("学生姓名", studentDesign.getStudentName());
            data.put("学生人数", design.getMaxRecv());
            data.put("实际课题名称", design.getSource());
            data.put("题目进行具体地点", design.getAddress());
            data.put("课题协作单位", design.getHelp());
            data.put("起止时间", "");
            data.put("上机时数", "");
            data.put(design.getType(), "√");
            datas.add(data);
        }

        if (!datas.isEmpty()) {
            dynamicOperateExcelUtils.appendRows(5, datas,
                    new String[]{"姓名", "职称", "毕业设计论文题目", "学生姓名", "学生人数",
                            "工程设计", "专题研究", "综合试验", "论文综述", "其他",
                            "实际课题名称", "题目进行具体地点", "课题协作单位", "起止时间", "上机时数"});
        } else {
            dynamicOperateExcelUtils.replaceCellValue(5, 0, "无相关记录");
        }
        dynamicOperateExcelUtils.replaceCellValue(0, 0, "毕业设计题目互选情况统计表");
        int row = datas.size() + 6;
        dynamicOperateExcelUtils.replaceCellValue(row, 0, "合计");
        dynamicOperateExcelUtils.replaceCellValue(row, 1, "题目：" + result.get("paper") + "项\n" +
                "教授：" + result.get("professor") + "人        副教授（高级工程师）：" + result.get("associateProfessor") + "人\n" +
                "讲师（工程师）：" + result.get("lecturer") + "人        助教：" + result.get("assistant") + "人");
        dynamicOperateExcelUtils.replaceCellValue(row, 4, result.get("studentNumber"));
        dynamicOperateExcelUtils.replaceCellValue(row, 5, result.get("gcsj"));
        dynamicOperateExcelUtils.replaceCellValue(row, 6, result.get("ztyj"));
        dynamicOperateExcelUtils.replaceCellValue(row, 7, result.get("zhsy"));
        dynamicOperateExcelUtils.replaceCellValue(row, 8, result.get("lwzs"));
        dynamicOperateExcelUtils.replaceCellValue(row, 9, result.get("qt"));
//        String fileName = "华中科技大学_" + college + "_" + major + "_本科毕业设计(论文)课题安排表.xlsx";
        String fileName = "华中科技大学";
        dynamicOperateExcelUtils.exportExcel(new File(BASEPATH + fileName));
        //
        fileService.downloadFile(fileName, request, response);
        fileService.deleteFile(BASEPATH + fileName);
    }


    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public String review(Integer id, String type, String studentUsername, Integer score, String level, String note) {
        StudentDesign studentDesign = studentDesignRepositoty.findByDesignIDAndStudentUsername(id, studentUsername);
        Design design = designRepository.findById(id).orElse(null);
        assert design != null;
        switch (type) {
            case ("开题报告"):
                if ("未批阅".equals(studentDesign.getReportReview())) {
                    designRepository.updateReportToReviewAndReportReviewedById(id, design.getReportToReview() - 1, design.getReportReviewed() + 1);
                }
                studentDesignRepositoty.updateReportScoreAndReportLevelAndReportNoteAndReportReviewByDesignIDAndStudentUsername(id, studentUsername, score, level, note, "已批阅");

                break;
            case ("文献翻译"):
                if ("未批阅".equals(studentDesign.getTransReview())) {
                    designRepository.updateTransToReviewAndTransReviewedById(id, design.getTransToReview() - 1, design.getTransReviewed() + 1);
                }
                studentDesignRepositoty.updateTransScoreAndTransLevelAndTransNoteAndTransReviewByDesignIDAndStudentUsername(id, studentUsername, score, level, note, "已批阅");
                break;
            case ("毕业论文"):
                if ("未批阅".equals(studentDesign.getPaperReview())) {
                    designRepository.updatePaperToReviewAndPaperReviewedById(id, design.getPaperToReview() - 1, design.getPaperReviewed() + 1);
                }
                studentDesignRepositoty.updatePaperScoreAndPaperLevelAndPaperNoteAndPaperReviewByDesignIDAndStudentUsername(id, studentUsername, score, level, note, "已批阅");
                break;
        }
        return "修改成功！";
    }

    @Override
    @CacheEvict(cacheNames = "design", allEntries = true)
    public Map<String, String> uploadReport(HttpServletRequest request, String category, Integer id) throws IOException {
        Map<String, String> result = fileService.summitFile(request, category, id);
        if ("上传成功！".equals(result.get("result"))) {
            LocalDate now = LocalDate.now();
            Design design = designRepository.findById(id).orElse(null);
            String username = userDetailsUtil.getUserDetails();
            StudentDesign studentDesign = studentDesignRepositoty.findByDesignIDAndStudentUsername(id, username);
            switch (category) {
                case ("毕业设计开题报告"):
                    //开题报告设成已提交
                    studentDesignRepositoty.updateReportFilenameAndReportSummitTimeByDesignIDAndStudentUsername(id, username, result.get("filename"), now);
                    if ("未提交".equals(studentDesign.getReportSummit())) {
                        //设成未批阅
                        studentDesignRepositoty.updateReportSummitAndReportReviewByDesignIDAndStudentUsername(id, username, "已提交", "未批阅");
                        designRepository.updateReportToSummitAndReportSummitedAndReportToReviewById(id, design.getReportToSummit() - 1, design.getReportSummited() + 1, design.getReportSummited() + 1 - design.getReportReviewed());
                    }
                    break;
                case ("毕业设计文献翻译"):
                    studentDesignRepositoty.updateTransFilenameAndTransSummitTimeByDesignIDAndStudentUsername(id, username, result.get("filename"), now);
                    if ("未提交".equals(studentDesign.getTransSummit())) {
                        studentDesignRepositoty.updateTransSummitAndTransReviewByDesignIDAndStudentUsername(id, username, "已提交", "未批阅");
                        designRepository.updateTransToSummitAndTransSummitedAndTransToReviewById(id, design.getTransToSummit() - 1, design.getTransSummited() + 1, design.getTransSummited() + 1 - design.getPaperReviewed());
                    }
                    break;
                case ("毕业设计毕业论文"):
                    studentDesignRepositoty.updatePaperFilenameAndPaperSummitTimeByDesignIDAndStudentUsername(id, username, result.get("filename"), now);
                    if ("未提交".equals(studentDesign.getPaperSummit())) {
                        studentDesignRepositoty.updatePaperSummitAndPaperReviewByDesignIDAndStudentUsername(id, username, "已提交", "未批阅");
                        designRepository.updatePaperToSummitAndPaperSummitedAndPaperToReviewById(id, design.getPaperToSummit() - 1, design.getPaperSummited() + 1, design.getPaperSummited() + 1 - design.getPaperReviewed());
                    }
                    break;
            }
        }
        return result;
    }

    @Override
    public Design getTeamById(Integer id) {
        return designRepository.findById(id).orElse(null);
    }

    @Override
    public void downloadDesign(String college, String grade, HttpServletRequest request, HttpServletResponse response) throws FileNotFoundException {
        List<Map<String, Object>> datas = new ArrayList<>();
        List<Design> designs = designRepository.findAllByCollegeAndGrade(college, grade);
        System.out.println(designs);
        // designs.sort(Comparator.comparing(Design::getCategory).thenComparing(Design::getTeacherUsername));
        DynamicOperateExcelUtils dynamicOperateExcelUtils = new DynamicOperateExcelUtils(BASEPATH + "毕业设计/" + "毕业设计_题目互选统计表模板.xlsx");
        for (Design design : designs) {
            Map<String, Object> data = new HashMap<>();
            data.put("题目名称", design.getName());
            data.put("教师", design.getTeacherUsername());
            data.put("所属院系", design.getCollege());
            data.put("所属系别", design.getCategory());
            if (design.getRecv() == null || design.getMaxRecv() == 0) {
                data.put("选择状态", " ");
            } else {
                data.put("选择状态", "已选择");
            }
            datas.add(data);
        }
        dynamicOperateExcelUtils.replaceCellValue(0, 0, college + grade + "毕业设计题目互选情况统计表");
        if (!datas.isEmpty()) {
            dynamicOperateExcelUtils.appendRows(5, datas,
                    new String[]{"题目名称", "教师", "所属院系", "所属系别", "选择状态",});
        } else {
            dynamicOperateExcelUtils.replaceCellValue(5, 0, "无相关记录");
        }

        //  String fileName = college + "_" + grade + "_    毕业设计_题目互选统计表.xlsx";
        String fileName = "扯淡.xlsx";
        dynamicOperateExcelUtils.exportExcel(new File(BASEPATH + fileName));
        fileService.downloadFile(fileName, request, response);
        //  fileService.deleteFile(BASEPATH + fileName);
    }


    @Override
    public Map<String, String> addTask(String category, Integer id, HttpServletRequest request) throws IOException {
        Map<String, String> result = fileService.summitFile(request, category, id);
        if ("上传成功！".equals(result.get("result"))) {
            designRepository.updateTaskByID(id, result.get("filename"));
        }
        return result;
    }

    @Override
    public String auditTask(Integer id, String taskAudit) {
        designRepository.updatetaskAuditByID(id, taskAudit);
        return "审核成功！";
    }

    private Map<String, Integer> count(List<StudentDesign> studentDesigns) {
        Map<String, Integer> result = new HashMap<>();
        result.put("paper", 0);
        result.put("professor", 0);
        result.put("associateProfessor", 0);
        result.put("lecturer", 0);
        result.put("assistant", 0);
        result.put("studentNumber", 0);
        result.put("gcsj", 0);//工程设计
        result.put("ztyj", 0);//专题研究
        result.put("zhsy", 0);//综合试验
        result.put("lwzs", 0);//论文综述
        result.put("qt", 0);//其他
        for (StudentDesign studentDesign : studentDesigns) {
            Design design = designRepository.findById(studentDesign.getDesignID()).orElse(null);
            assert design != null;
            result.put("paper", result.get("paper") + 1);
            result.put("studentNumber", result.get("studentNumber") + 1);
            switch (design.getTitle()) {
                case ("教授"):
                    result.put("professor", result.get("professor") + 1);
                    break;
                case ("副教授"):
                    result.put("associateProfessor", result.get("associateProfessor") + 1);
                    break;
                case ("讲师"):
                    result.put("lecturer", result.get("lecturer") + 1);
                    break;
                case ("助教"):
                    result.put("assistant", result.get("assistant") + 1);
                    break;
            }
            switch (design.getType()) {
                case ("工程设计"):
                    result.put("gcsj", result.get("gcsj") + 1);
                    break;
                case ("专题研究"):
                    result.put("ztyj", result.get("ztyj") + 1);
                    break;
                case ("综合试验"):
                    result.put("zhsy", result.get("zhsy") + 1);
                    break;
                case ("论文综述"):
                    result.put("lwzs", result.get("lwzs") + 1);
                    ;
                    break;
                case ("其他"):
                    result.put("qt", result.get("qt") + 1);
                    break;
            }
        }
        return result;
    }
}


